{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## github地址：\n",
    "https://github.com/aio-libs/aiomysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## 主文件\n",
    "\n",
    "import tornado\n",
    "from aiomysql import create_pool\n",
    "\n",
    "class MainHandler(tornado.web.RequestHandler):\n",
    "    def initialize(self, db):\n",
    "        self.db = db\n",
    "    \n",
    "    ## 获取数据\n",
    "    async def get(self, *args, **kwargs):\n",
    "        id = \"\"\n",
    "        name = \"\"\n",
    "        email = \"\"\n",
    "        address = \"\"\n",
    "        message = \"\"\n",
    "        \n",
    "        async with create_pool(host=self.db[\"host\"], port=self.db[\"port\"],\n",
    "                          user=self.db[\"user\"], password=self.db[\"password\"],\n",
    "                          db=self.db[\"name\"], charset=\"utf8\") as pool:\n",
    "            async with pool.acquire() as conn:\n",
    "                async with conn.cursor() as cur:\n",
    "                    await cur.execute(\"SELECT id, name, email, address, message FROM message\")\n",
    "                    try:\n",
    "                        ## tuple取值\n",
    "                        id, name, email, address, message = await cur.fetchone()\n",
    "                    except Exception as e:\n",
    "                        ## 数据为空\n",
    "                        pass\n",
    "        \n",
    "        self.render(\"message.html\", id=id, name=name, email=email, address=address, message=message)\n",
    "    \n",
    "    ## 写入数据\n",
    "    async def post(self, *args, **kwargs):\n",
    "        id = self.get_body_argument(\"id\", \"\")\n",
    "        name = self.get_body_argument(\"name\", \"\")\n",
    "        email = self.get_body_argument(\"email\", \"\")\n",
    "        address = self.get_body_argument(\"address\", \"\")\n",
    "        message = self.get_body_argument(\"message\", \"\")\n",
    "        \n",
    "        async with create_pool(host=self.db[\"host\"], port=self.db[\"port\"],\n",
    "                          user=self.db[\"user\"], password=self.db[\"password\"],\n",
    "                          db=self.db[\"name\"], charset=\"utf8\") as pool:\n",
    "            async with pool.acquire() as conn:\n",
    "                async with conn.cursor() as cur:\n",
    "                    if not id:\n",
    "                        await cur.execute(\"INSERT INTO message(name, email, address, message) VALUES({},{},{},{})\".format(name, email, address, message))\n",
    "                    else:\n",
    "                        await cur.execute(\"UPDATE message SET name='{}', email='{}', address='{}', message='{}' WHERE id={})\".format(name, email, address, message, id))\n",
    "                    ## 提交insert 语句\n",
    "                    await conn.commit()\n",
    "        \n",
    "        self.render(\"message.html\", id=id, name=name, email=email, address=address, message=message)\n",
    "    \n",
    "    \n",
    "settings = {\n",
    "    \"static_path\": \"path/to/static\",\n",
    "    \"static_url_prefix\": \"/static/\",\n",
    "    \"template_path\": \"templates\",\n",
    "    \"db\": {\n",
    "        \"host\": \"127.0.0.1\",\n",
    "        \"port\": 3306\n",
    "        \"user\": \"root\",\n",
    "        \"password\": \"123456\",\n",
    "        \"name\": \"message\",\n",
    "    }\n",
    "}\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app = tornado.web.Application([\n",
    "        (\"/\", MainHandler, {\"db\": settings[\"db\"]}),\n",
    "        (\"/static/(.*)\", StaticFileHandler, {\"path\": \"path/to/static\"})\n",
    "    ], debug=True, **settings)\n",
    "    app.listen(8000)\n",
    "    tornado.ioloop.IOLoop.current().start()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import asyncio\n",
    "from aiomysql import create_pool\n",
    "\n",
    "async def go():\n",
    "    async with create_pool(host=\"127.0.0.1\", port=3306,\n",
    "                          user=\"root\", password=\"123456\",\n",
    "                          db=\"message\", charset=\"utf8\") as pool:\n",
    "        async with pool.acquire() as conn:\n",
    "            async with conn.cursor() as cur:\n",
    "                await cur.execute(\"SELECT * FROM message\")\n",
    "                value = await cur.fetchone()\n",
    "                print(value)\n",
    "                \n",
    "if __name__ == \"__main__\":\n",
    "#     loop = asyncio.get_event_loop()\n",
    "#     loop.run_until_complete(go())\n",
    "\n",
    "    from tornado import gen, httpclient, ioloop\n",
    "    io_loop = ioloop.IOLoop.current()\n",
    "    io_loop.run_sync(go)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
